package com.cpi.cornpp.bushelversion.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.bushelversion.dto.BushelsVersionDTO;
import com.cpi.cornpp.bushelversion.exception.BushelsVersionException;
import com.cpi.cornpp.common.exception.CornPPException;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;
/**
 * 
 * @description: This Class is used to handle the Bushels Version Data Access
 * @author <strong> Edgar Joao </strong>
 * @version 1.0
 * @see DBDAO
 */
public class BushelsVersionDAO extends DBDAO{
	
	private static final String SELECT_MONTH_AND_BUSHELS_VERSIONS = "SELECT bushel_version_id, MON.MONTH_PERIOD_VERSION_ID, bus.amount, bus.CORN_ORIGIN_ID, corns_origins.description FROM MONTHS_PERIODS_VERSIONS MON INNER JOIN BUSHELS_VERSIONS BUS ON MON.MONTH_PERIOD_VERSION_ID = BUS.MONTH_PERIOD_VERSION_ID INNER JOIN MONTHS_PERIODS MP ON MON.MONTH_PERIOD_ID = MP.MONTH_PERIOD_ID INNER JOIN CORNS_ORIGINS ON CORNS_ORIGINS.CORN_ORIGIN_ID = bus.CORN_ORIGIN_ID WHERE MON.VERSION_ID = ? order by bus.CORN_ORIGIN_ID, MP.START_DATE";//$NON-NLS-1$
	private static final String UPDATE_BUSHELS_VERSIONS = "UPDATE BUSHELS_VERSIONS set amount = ? WHERE bushel_version_id = ?";//$NON-NLS-1$
	
	/**
	 * Lista de Bushels Versions
	 * 
	 * @author Edgar Joao
	 * @param versionId
	 * @return
	 * @throws BushelsVersionException
	 */
	public List getBushelsVersions(int versionId) throws BushelsVersionException{
		List listOfBushelsVersions = null;
		PreparedStatement preparedStatement = null;
		BushelsVersionDTO bushelsVersionDTO = null;
		ResultSet rs = null;
		try{				
			listOfBushelsVersions = new ArrayList(0);			
			preparedStatement = prepareSQL(SELECT_MONTH_AND_BUSHELS_VERSIONS, dsName);
			preparedStatement.setInt(1, versionId);	
			rs = executeQuery(preparedStatement);			
			while (rs.next()){
				bushelsVersionDTO = new BushelsVersionDTO();
				bushelsVersionDTO.setBushelVersionId(rs.getInt(1));
				bushelsVersionDTO.setMonthPeriodId(rs.getInt(2));
				bushelsVersionDTO.setAmount(rs.getDouble(3));
				bushelsVersionDTO.setCornOriginId(rs.getString(4));
				bushelsVersionDTO.setCornOrigindDescription(rs.getString(5));
				listOfBushelsVersions.add(bushelsVersionDTO);
			}	
		}catch (PoolException poolException){
			BushelsVersionException bushelsVersionException = null;
			poolException.printStackTrace(System.out);
			bushelsVersionException = new BushelsVersionException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw bushelsVersionException;	
		} catch (SQLException sqlException) {
			BushelsVersionException bushelsVersionException = null;
			sqlException.printStackTrace(System.out);
			bushelsVersionException = new BushelsVersionException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw bushelsVersionException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
		return listOfBushelsVersions;		
	}
	/**
	 * Update Bushels Versions
	 * @param dto
	 * @throws BushelsVersionException
	 */
	public void updateBushelsVersions(BushelsVersionDTO dto) throws BushelsVersionException{
		PreparedStatement preparedStatement = null;				
		try{										
			preparedStatement = prepareSQL(UPDATE_BUSHELS_VERSIONS, dsName);		
			preparedStatement.setDouble(1, dto.getAmount());
			preparedStatement.setInt(2, dto.getBushelVersionId());																
			preparedStatement.executeUpdate();						
		}catch (PoolException poolException){
			BushelsVersionException plantVersionException = null;
			poolException.printStackTrace(System.out);
			plantVersionException = new BushelsVersionException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_UPDATE);
			throw plantVersionException;	
		} catch (SQLException sqlException) {
			BushelsVersionException bushelsVersionException = null;
			sqlException.printStackTrace(System.out);
			bushelsVersionException = new BushelsVersionException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_UPDATE);
			throw bushelsVersionException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
	}
}
